import os
import sqlite3

from typing import Iterable


class Database_Integration_Operations:
    '''属于数据库集成操作类，提供操作数据库的各种底层方法'''
    if True:
        TableName_temp = '____temp____'
        TableName_memory = ':memory:'
        TableName_sqlite_sequence = 'sqlite_sequence'

        AttrString_IdValue = 'IdValue'

    # 建立游标对象
    class __Cursor:
        def __init__(self, con: sqlite3.Connection) -> None:
            self.__cur = con.cursor()
    # 返回对象
        def __enter__(self):
            return self.__cur
    # 关闭对象
        def __exit__(self, exc_type, exc_val, exc_tb):
            self.__cur.close()

    # 表类
    class _Table:
        def __init__(self, dio: 'Database_Integration_Operations', TableName: str):
            self.__dio = dio
            self.__TableName = TableName
        # 替换名字
        def alterName(self, name: str):
            return self.__dio.alterTableName(self.__TableName, name)

        # 添加记录
        def appendItem(self, ColumnNameList: Iterable[str], ItemValueList: Iterable):
            return self.__dio.appendItem(
                self.__TableName,
                ColumnNameList,
                ItemValueList
            )

        # 修改记录
        def updateItem(self, IdValue: int, ColumnNameList: Iterable[str], ItemValueList: Iterable):
            return self.__dio.updateItem(
                self.__TableName,
                IdValue,
                ColumnNameList,
                ItemValueList
            )
        # 删除记录
        def deleteItem(self, IdValue: int):
            return self.__dio.deleteItem(self.__TableName, IdValue)

        def selectTableAllItems(
            self,
            goal='*',
            limit: int = None,
            offset: int = None
        ) -> list[tuple]:
            return self.__dio.selectTableAllItems(
                self.__TableName,
                goal,
                limit,
                offset
            )

    def __init__(self, FilePath: str = ':memory:'):
        self.__con = sqlite3.connect(FilePath, check_same_thread=False)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def getCursor(self):
        return self.__Cursor(self.__con)

    # 获取表名
    def getTableNames(self) -> list[str]:
        with self.getCursor() as _cur:
            _cur.execute(
                f'''SELECT name FROM sqlite_master WHERE type= 'table' order by name'''
            )
            _filter = filter(
                lambda NameTuple: NameTuple[0] != self.TableName_sqlite_sequence,
                _cur.fetchall()
            )
            return [item[0] for item in _filter]

    # 创建表
    def createTable(self, TableName: str):
        with self.getCursor() as _cur:
            sql = f'CREATE TABLE IF NOT EXISTS {TableName} (IdValue INTEGER PRIMARY KEY AUTOINCREMENT)'
            _cur.execute(sql)
        return self.getTable(TableName)

    # 删除表
    def dropTable(self, TableName: str):
        with self.getCursor() as _cur:
            sql = f'''DROP TABLE IF EXISTS {TableName};'''
            _cur.execute(sql)
    # 读取表
    def getTable(self, TableName: str):
        return self._Table(self, TableName)

    # 添加数据段
    def appendItem(self, TableName: str, ColumnNameList: Iterable[str], ItemValueList: Iterable):
        with self.getCursor() as _cur:
            sql = f'''INSERT into "{TableName}"({','.join(ColumnNameList)}) values({','.join(['?']*len(ItemValueList))})'''
            _cur.execute(sql, ItemValueList)

    # 更新数据段
    def updateItem(self, TableName: str, IdValue: int, ColumnNameList: Iterable[str], ItemValueList: Iterable):
        with self.getCursor() as _cur:
            sql = f'''
                    UPDATE "{TableName}"
                    SET {'=?,'.join(ColumnNameList)+'=?'}
                    WHERE IdValue=?
                '''
            _cur.execute(sql, (*ItemValueList, IdValue))

    # 删除数据段
    def deleteItem(self, TableName: str, IdValue: int):
        with self.getCursor() as _cur:
            _cur.execute(
                f'''DELETE from "{TableName}" where IdValue=?''', (IdValue,))

    # 读取表全部数据
    def selectTableAllItems(
        self, TableName: str,
        goal='*',
        limit: int = None,
        offset: int = None
    ) -> list[tuple]:
        with self.getCursor() as _cur:
            sql = f'SELECT {goal} FROM {TableName}'
            if limit is not None:
                sql += f' LIMIT {limit}'
            if offset is not None:
                sql += f' OFFSET {offset}'

            _cur.execute(sql)
            return _cur.fetchall()

    # 保存更改
    def save(self):
        self.__con.commit()

    # 关闭表（保存数据）
    def close(self, save=True):
        if save:
            self.save()
        self.__con.close()


class DIOMgr_base:
    def __init__(self, dio: Database_Integration_Operations):
        self.dio = dio

    def save(self):
        self.dio.save()

    def close(self, save=True):
        self.dio.close(save)
